PostgreSQL Chapter 15. Parallel Query

Chapter 15. Parallel Query

1. 為了更快回應查詢 PostgreSQL 設計的 query plan 可利用多個 CPU
2. 很多查詢沒辦法受益於 parallel query 因為現在實作的限制，或是沒能找到比 serial 更快的 query plan
3. 通常受益最多的查詢回傳少數 row ，但觸及大量資料

15.1. How Parallel Query Works

EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
--                                     QUERY PLAN
-- -----------------------------------------------------------------------------------
--  Gather  (cost=1.00..2161.93 rows=1 width=97)
--    Workers Planned: 2
--    ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..2160.83 rows=1 width=97)
--          Filter: (filler ~~ '%x%'::text)
-- (4 rows)

1. plan tree
   1. 當 optimizer 判定 parallel query 是比較快的策略，就會建立包含 Gather 或 Gather Merge node 的 query plan
   2. 在所有情況下 Gather or Gather Merge node 只會有剛好一個 child plan node，child plan 是 plan 的一部分，會被平行執行
   3. 當 Gather or Gather Merge node 在 plan tree 的頂端，整個 query 都會平行執行；如果是在 child node ，則只有其底下的 plan 才會平行執行
   4. 在範例中的 query 只有用到一張 table ，除了 Gather node 本身之外只有一個 plan node； 這個 plan node 是 Gather node 的 child ，因此他會平行執行
2. worker
   1. 使用 EXPLAIN 可以看到 plan 的 worker 數量，當 query 處理到 Gather node 時，會產生該數量的 background worker processes
   2. planner 最多會產生的 worker 數量是參數 max_parallel_workers_per_gather； background workers 的總數受參數 max_worker_processes 和 max_parallel_workers 限制
   3. 有可能會發生，使用的 worker 數量比 plan 的還要少，甚至沒有 worker ，所以可能會因為造成效能很差。
   4. 如果這個情況常發生可以考慮加大 max_worker_processes 和 max_parallel_workers 以增加 worker ；或是減小 max_parallel_workers_per_gather 減少單一 plan 要的 worker 數量
   5. 每個 background worker process 都會執行一部分 query 包括 leader，但是 leader 還需要讀取 worker 產生的所有資料(tuple)
   6. 當產生的 tuple 數量少的時候， leader 行為會像 worker 幫忙加速查詢；如果產生的 tuple 數量多， leader 幾乎會整個被指派去讀 worker 產生的 tuple 及執行 Gather node 或 Gather Merge node 上層的 plan node 所要求的工作，在這個狀況， leader 只會執行很少的平行工作。
   7. When the node at the top of the parallel portion of the plan is Gather Merge rather than Gather, it indicates that each process executing the parallel portion of the plan is producing tuples in sorted order, and that the leader is performing an order-preserving merge. In contrast, Gather reads tuples from the workers in whatever order is convenient, destroying any sort order that may have existed.

註:

• worker 是 single-thread process
• Gather Merge 會在使用到 ORDER BY 語句的 parallel query 出現，使用 binary heap 。
• 使用 parallel 時 plan tree 就會出現 Gather/(Merge) node ，該 node 只會有一個 child ， child 是 parallel 的
• max_parallel_workers_per_gather
  • Type:integer
  • Default:2
  • Min:0 / Max:1024
  • Restart:false
  • 限制單一 Gather / (Merge) node 的 worker 數量
  • request 的 worker 數量可能不夠，所以 plan 效率可能會比較差
  • 設為 0 會停用 parallel query
  • 一個 parallel query 如果使用了 N 個 worker 代表其可能使用了最多 N+1 倍的資源
• max_worker_processes
  • Type:integer
  • Default:8
  • Min:0 / Max:262143
  • Restart:true
  • 限制最大的 background processes 數量
  • standby server 的數值不能小於 primary server ，不然會無法 query
  • 建議一併調整 max_parallel_workers max_parallel_maintenance_workers max_parallel_workers_per_gather
  • parallel worker 會從 pool 取得
• max_parallel_workers
  • Type:integer
  • Default:8
  • Min:0 / Max:1024
  • Restart:false
  • 限制同一時間 database cluster 可用的 parallel worker processes。不能大於 max_worker_processes
  • parallel worker 的數量被此參數限制
  • 加大時建議一併調整 max_parallel_maintenance_workers max_parallel_workers_per_gather
  • 設定的比 max_worker_processes 是沒有用的

15.2. When Can Parallel Query Be Used?

• max_parallel_workers_per_gather > 0
• 當 OS 是 single-user mode 時不會使用 parallel query，因為沒辦法用 background worker
• 以下情況不會使用 parallel query:
  • query 寫資料或是鎖定任何 row 的時候。如果 query 包含修改資料的操作或是 CTE 不行。以下為搭配 SELECT 使用的例外:
    • CREATE TABLE ... AS
    • SELECT INTO
    • CREATE MATERIALIZED VIEW
    • REFRESH MATERIALIZED VIEW
  • 當 query 可能會被暫停的時候。例如 DECLARE CURSOR、FOR x IN query LOOP .. END LOOP 不可能使用 parallel plan。
  • query 用到標記為 PARALLEL UNSAFE 的 function 的時候。
  • 當 query 已經在平行的 query 的時候。例如 parallel query 呼叫 function 去做 query 不會使用 parallel plan。因為這可能會需要很多 process ，所以實作會保持目前的限制。
• 就算產生了 parallel query plan ，但是在執行時可能無法執行該 parallel query ， leader 會自行處理，就算是 Gather node 還沒出現。會在以下情形發生：
  • 若 parallel 會使 background workers 數量超過 max_worker_processes
  • 若 parallel 會使執行中的 background workers 數量超過 max_parallel_workers

15.3.1. Parallel Scans

• parallel sequential scan: table blocks 會被劃分成多個範圍，在 process 之間共用。 每個 worker process 會在完成目前的 table blocks 範圍才會再去拿別的 block。
• parallel bitmap heap scan: 有一個 process 被選為 leader，他會 scan 一個或是多個 index ，還有建立 bitmap 指示哪些 table block 需要被 scan 。 接著這些 block 會被分配給 process 執行 parallel sequential scan 。 也就是說 heap scan 是平行的，底層的 index scan 不是。
• parallel index scan 或 parallel index-only scan: process 輪流從 index 讀取資料。目前 parallel index scans 只有 btree index 可用 。每個 process 都會認領一個 index block 然後 scan 及回傳所有該 block 參照的 tuple ，同時其他 process 可以回傳不同 index block 的 tuple 。在每個 worker process 內， parallel btree scan 的結果都會排序。

15.3.2. Parallel Joins

與 non-parallel plan 一樣，可以使用 nested loop, hash join, merge join 將 table 與一張或是多張表做 join 。 如果可以安全執行的話，inner side 可以是任何 planner 支援的 non-parallel plan 。有的 join 其 inner side 也可以是 parallel plan 。

• nested loop join: inner side 一律為 non-parallel 。如果 inner side 是 index scan 效率會比較好，因為外部可以將 index 查找分配到多個 process
• merge join: inner side 一律為 non-parallel 。效率可能不好，尤其是要排序時，因為工作和結果會在每個 process 中重複。
• hash join (without the "parallel" prefix): inner side 每個 process 都會複製一份 hash table 。 如果 hash table 很大或是 plan 開銷大，這樣效率會很低。 在 hash join 中 inner side 是一個 parallel hash ，它把 shared hash table 分配到多個 process。

15.3.3. Parallel Aggregation

PostgreSQL 透過兩個階段的 aggregation 去支援平行。第一步，每個 query 的 process 執行一個 aggregation step ，產生一部分的結果，這在 plan 中是一個 Partial Aggregate node 。第二步，部分的結果透過 Gather 或 Gather Merge 傳送到 leader 。最後 leader 重新 aggregate 來自 workers 的結果作為最終結果。這在 plan 中是一個 Finalize Aggregate node 。

因為 Finalize Aggregate node 是在 leader process 執行， query planner 不喜歡分組數相對大量的 query 。例如最壞的情形是， Finalize Aggregate node 看到的分組數量和 Partial Aggregate 的 input rows 數量，這種情形，使用 parallel aggregation 明顯效能很差，所以 query planner 不會傾向使用 parallel aggregate 的做法。

Parallel aggregation 不是在所有情況下都支援，每個 aggregate 必須是 safe for parallelism 且必須有一個 combine function 。如果 aggregate 有一個 internal 類型的 transition state ，他必需要有 serialization function 和 deserialization function 。 詳細資訊可看 CREATE AGGREGATE 。 如果 aggregate function call 有 DISTINCT or ORDER BY 不會支援 parallel aggregation ， ordered set aggregates 或是 GROUPING SETS 也不支援。只有 query 的所有 join 也是 plan 的平行部分之一才支援。

15.3.4. Parallel Append

當 PostgreSQL 需要結合多個來源的 row 為一個 result set 時，會使用 Append plan node 或 MergeAppend plan node ，使用 UNION ALL 或是掃描 partitioned table 的時候會常發生，就像是這些 node 可以被用在其他 plan 一樣，也可以用在 parallel plan ，不過在 parallel plan ， planner 使用的是 Parallel Append node 。

當 Append node 被使用在 parallel plan 時，每個 process 都會依照出現的順序執行 child plan。 使用 Parallel Append 時 executor 會盡量平均分散 child plan ， 讓 child plan 同時執行。這樣可以避免競爭也避免啟動 child plan 的 process 但卻沒有使用到。

在 parallel plan 時，與一般的 Append node 不同（只能有只能有 partial child plans ）， Parallel Append node 可以有 partial child plans 與 non-partial child plans 。 Non-partial children 只會被一個 process 掃描，因為掃超過一次會產生重複的結果。因此，即使沒有有效的 partial plan 可用，涉及附加多個 results set 的 plan 也可以實現粗粒度的並行性。例如，一個針對 partitioned table 的查詢，該查詢只能通過使用不支援 parallel scan 的 index 來有效率地實現。 planner 可能會選擇一般的 Index Scan plan 的 Parallel Append ，每個單獨的 index scan 都會被一個 process 執行，但不同的 process 可以同時執行不同的掃描。

enable_parallel_append 可以關閉這個功能。

15.3.5. Parallel Plan Tips

如果 query 沒有生成預期的 parallel plan ，可以嘗試減小 parallel_setup_cost 或 parallel_tuple_cost 。 plan 也可能變得比 serial plan 慢，但不常發生。如果參數都設定很小或是零，還是沒有產生 parallel plan ，可能有別的原因，可以參考 Section 15.2 和 Section 15.4 。

執行 parallel plan 時可以使用 EXPLAIN (ANALYZE, VERBOSE) 查看給個 plan node 的每個 worker 的詳情。

15.4. Parallel Safety

planner 把操作分成 PARALLEL SAFE PARALLEL RESTRICTED PARALLEL UNSAFE 。

• PARALLEL SAFE 不會和 parallel query 衝突。
• PARALLEL RESTRICTED 不能再 parallel worker 執行，但是當 parallel query 的時候可以在 leader 執行，因此 PARALLEL RESTRICTED 不可能出現在 Gather node 或 Gather Merge node 之下，但可以出現在其他位置。
• PARALLEL UNSAFE 不能在 parallel query 執行，也不能在 leader 執行，當 query 包含任何 PARALLEL UNSAFE 的操作， parallel query 就會完全禁用。

以下操作是 PARALLEL RESTRICTED :

• CTE 的掃描
• temporary table 的掃描
• foreign tables 的掃描 (IsForeignScanParallelSafe 除外)
• 有 InitPlan 的 plan node
• 引用 SubPlan 的 plan node

15.4.1. Parallel Labeling for Functions and Aggregates

planner 不會自動判斷 user-defined 的 function 或 aggregate 是 PARALLEL SAFE, PARALLEL RESTRICTED, PARALLEL UNSAFE ，除非被標記不然都視為 PARALLEL UNSAFE 。使用 CREATE FUNCTION 或 ALTER FUNCTION 可以指定 PARALLEL SAFE PARALLEL RESTRICTED PARALLEL UNSAFE 。使用 CREATE AGGREGATE 時 PARALLEL 可以指定 SAFE RESTRICTED UNSAFE 。

如果 function 和 aggregate 會 寫入db access sequence 改變 transaction 狀態 更改設定 那一定要被標為 PARALLEL UNSAFE ，通樣如果 function 會 access temporary tables access client connection state access cursors ... 等等或無法在 worker 同步的 backend-local state 那一定要被標為 PARALLEL RESTRICTED 。 例如 setseed 和 random。

一般而言，如果一個 function 的平行標記虛報了，把他用在 parallel query 中可能會報錯或是拿到錯誤的答案；如果是 C 語言的 function 那會更嚴重。所以有任何疑慮，最好標為 UNSAFE 。

如果在 parallel worker 中的 function 要求一個不是 leader 鎖擁有的 lock ，例如 query 一張 query 沒有參照的 table ，則該 lock 會在 worker exit 的時候 release ，而不是 transaction 結束的時候。所以這種函數要被標為 PARALLEL RESTRICTED 讓他只在 leader 執行。

query planner 不會為了生成更好的 plan 而延遲評估 PARALLEL RESTRICTED 的 function 或 aggregate ，所以如果 WHERE 語句對一張 table 是 PARALLEL RESTRICTED 那 query planner 就不會在 parallel 的部分執行該 table 的掃描。有的情況下，在 parallel 的部分延遲評估 WHERE 語句是可行或是比較有效率的，但是 planner 不會這樣做。

refs

• doc: https://www.postgresql.org/docs/15/parallel-query.html
• plannodes: https://github.com/postgres/postgres/blob/c5dc80c1bc216f0e21a2f79f5e0415c2d4cfb35d/src/include/nodes/plannodes.h#L955-L994